from sqlalchemy import create_engine, Column, Integer, String, Date, DateTime
from sqlalchemy.orm import sessionmaker, declarative_base
import datetime
from datetime import date
import sqlite3
import schedule
import time

# 配置 MySQL 数据库连接
mysql_engine = create_engine(
    "mysql+pymysql://enigma:YGM123456rds@rm-2ze9zw019dkj6tq3xho.mysql.rds.aliyuncs.com:3306/deal",
    connect_args={}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=mysql_engine)
Base = declarative_base()


# 定义 MySQL 数据模型
class Stock(Base):
    __tablename__ = "stocks"

    id = Column(Integer, primary_key=True, index=True)
    type = Column(String(255), nullable=False, comment='类型')
    stock = Column(String(100), nullable=False, comment='交易码')
    created_at = Column(DateTime, default=datetime.datetime.utcnow, comment='创建时间')


Base.metadata.create_all(bind=mysql_engine)


def get_stocks_data_for_today():
    db = SessionLocal()
    try:
        today = date.today()
        # 查询当天的数据
        stocks = db.query(Stock).filter(
            Stock.created_at >= datetime.datetime.combine(today, datetime.time.min)).all()
        for stock in stocks:
            print(f"ID: {stock.id}, Date: {stock.created_at}")
        return stocks
    except Exception as e:
        print(f"查询数据时出错: {e}")
    finally:
        db.close()


def write_to_sqlite(data):
    try:
        # 连接到本地 SQLite 数据库
        sqlite_conn = sqlite3.connect('local_stocks.db')
        cursor = sqlite_conn.cursor()

        # 创建表（如果不存在）
        create_table_query = """
       CREATE TABLE IF NOT EXISTS order_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            stock TEXT,
            trans_type INTEGER,
            log_type INTEGER,
            single_res INTEGER DEFAULT 0,
            deal_res INTEGER DEFAULT 0,
            remark TEXT,
            price REAL,
            single_volume INTEGER,
            deal_volume INTEGER,
            created_at TEXT
        );
        """
        cursor.execute(create_table_query)
        print("写入成功")

        # 插入数据
        for stock in data:
            insert_query = "INSERT INTO stocks (id, type, stock, created_at) VALUES (?,?,?,?)"
            #cursor.execute(insert_query, (stock.id, stock.type, stock.stock, str(stock.created_at)))

        # 提交事务
        sqlite_conn.commit()
        print("数据成功写入本地 SQLite 数据库")
    except Exception as e:
        print(f"写入 SQLite 数据库时出错: {e}")
    finally:
        if sqlite_conn:
            sqlite_conn.close()


def job():
    data = get_stocks_data_for_today()
    if data:
        write_to_sqlite(data)


if __name__ == "__main__":
    job()


    # 每天 11 点执行任务
    #schedule.every().day.at("11:00").do(job)

    #while True:
        #schedule.run_pending()
        #time.sleep(1)

#nohup python3 schedule_stock_data.py &